package DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Vector;

import DataObjects.Bon;
import DataObjects.Menu;
import DataObjects.MetaDataOrder;
import DataObjects.Recipe;
import DataObjects.RecipeAdditions;


public class GetAllMenuesAndOrdersForATable {
	
	Vector<Menu> menues;
	Vector<Recipe> recipes;
	String tableNo;
	Vector<Bon> vecBon;
	MetaDataOrder metadataOrder;
	
	
	public GetAllMenuesAndOrdersForATable(String tableNo)
	{
		this.tableNo=tableNo;
		menues=new Vector<Menu>();
		recipes=new Vector<Recipe>();
		metadataOrder=new MetaDataOrder();
		
	}

	
	
	public void recieveMenues()
	{
		SQLConnection sc=new SQLConnection();
		
		
		try {
		sc.getConnection().setAutoCommit(false);
		
		PreparedStatement ps1 = sc.getConnection().prepareStatement("SELECT M.mid, M.Mname, M.Mprice FROM menu M  WHERE M.Mstatus='פעיל' AND M.mtype='business';");
		PreparedStatement ps2 = sc.getConnection().prepareStatement("SELECT R.Rid,R.Rname,M.mprice,RM.role,RM.isSelectable,R.actionlevel,R.extra,R.souce FROM  recipe_in_menu RM, recipe R ,menu M WHERE  M.mid=? AND M.mid=RM.mid AND RM.Rid=R.Rid AND R.Rstatus='פעיל'   ;");
		PreparedStatement ps3 = sc.getConnection().prepareStatement("SELECT M.mid, M.Mname FROM menu M WHERE M.mtype='simple' AND M.Mstatus='פעיל' ;");
		PreparedStatement ps4 = sc.getConnection().prepareStatement("SELECT R.Rid,R.Rname,RM.price,R.actionlevel,R.extra,R.souce FROM  recipe_in_menu RM, recipe R, menu M WHERE M.mid=? AND M.mid=RM.mid AND RM.Rid=R.Rid AND R.Rstatus='פעיל';");
		
		
		ResultSet rs1=ps1.executeQuery();
		while(rs1.next())
		{
			Menu menu=new Menu();
			menu.setType("business");
			menu.setId(rs1.getString(1));
			menu.setName(rs1.getString(2));
			menu.setPrice(rs1.getString(3));
			ps2.setString(1,rs1.getString(1));
			ResultSet rs2=ps2.executeQuery();
			Vector<Recipe> recipesOfBisnessMenu=new Vector<Recipe>();
			while(rs2.next())
			{
				Recipe rec=new Recipe();
				rec.setId(rs2.getString(1));
				rec.setName(rs2.getString(2));
				rec.setPrice(rs2.getString(3));
				rec.setRole(rs2.getString(4));
				rec.setIsSelectable(rs2.getInt(5));
				rec.setHasActionLevel(rs2.getInt(6));
				
				//proveraem esli est tosafot ili sousi u dannogo retsepta
				int midExtra=rs2.getInt(7);
				int midSouce=rs2.getInt(8);
				
				if (midExtra>0)
				{
					Vector<RecipeAdditions> extraVector=new Vector<RecipeAdditions>();
					PreparedStatement psExtra = sc.getConnection().prepareStatement("SELECT R.Rid,R.Rname FROM  recipe_in_menu RM, recipe R, menu M WHERE M.mid=? AND M.mid=RM.mid AND RM.Rid=R.Rid; ");
					psExtra.setInt(1,midExtra);
					ResultSet rsExtra=psExtra.executeQuery();
					while(rsExtra.next())
					{
						RecipeAdditions extraRecipe=new RecipeAdditions();
						extraRecipe.setId(Integer.toString(rsExtra.getInt(1)));
						extraRecipe.setName(rsExtra.getString(2));
						extraVector.add(extraRecipe);
					}
					rec.setExtraVector(extraVector);
					psExtra.close();
					rsExtra.close();
				}
					
				
				if (midSouce>0)
				{
					Vector<RecipeAdditions> souceVector=new Vector<RecipeAdditions>();
					PreparedStatement psSouce = sc.getConnection().prepareStatement("SELECT R.Rid,R.Rname FROM  recipe_in_menu RM, recipe R, menu M WHERE M.mid=? AND M.mid=RM.mid AND RM.Rid=R.Rid; ");
					psSouce.setInt(1,midSouce);
					ResultSet rsSouce=psSouce.executeQuery();
					while(rsSouce.next())
					{
						RecipeAdditions souceRecipe=new RecipeAdditions();
						souceRecipe.setId(Integer.toString(rsSouce.getInt(1)));
						souceRecipe.setName(rsSouce.getString(2));
						souceVector.add(souceRecipe);
					}
					rec.setSouceVector(souceVector);
					psSouce.close();
					rsSouce.close();
				}
				
				recipesOfBisnessMenu.add(rec);	
			}
			
			menu.setRecipes(recipesOfBisnessMenu);
			menues.add(menu);
			rs2.close();
		}
		
		ps1.close();
		ps2.close();
		rs1.close();
		
		
		ResultSet rs3=ps3.executeQuery();
		while(rs3.next())
		{
			Menu menu=new Menu();
			menu.setType("simple");
			menu.setId(rs3.getString(1));
			menu.setName(rs3.getString(2));
			menu.setPrice("0");
			ps4.setString(1,rs3.getString(1));
			ResultSet rs4=ps4.executeQuery();
			Vector<Recipe> recipesOfSimpleMenu=new Vector<Recipe>();
			while(rs4.next())
			{
				Recipe rec=new Recipe();
				rec.setId(rs4.getString(1));
				rec.setName(rs4.getString(2));
				rec.setPrice(rs4.getString(3));
				rec.setRole("0");
				int t=1;
				rec.setIsSelectable(t);
				rec.setHasActionLevel(rs4.getInt(4));
				
				//proveraem esli est tosafot ili sousi u dannogo retsepta
				int midExtra=rs4.getInt(5);
				int midSouce=rs4.getInt(6);
				
				if (midExtra>0)
				{
					Vector<RecipeAdditions> extraVector=new Vector<RecipeAdditions>();
					PreparedStatement psExtra = sc.getConnection().prepareStatement("SELECT R.Rid,R.Rname FROM  recipe_in_menu RM, recipe R, menu M WHERE M.mid=? AND M.mid=RM.mid AND RM.Rid=R.Rid; ");
					psExtra.setInt(1,midExtra);
					ResultSet rsExtra=psExtra.executeQuery();
					while(rsExtra.next())
					{
						RecipeAdditions extraRecipe=new RecipeAdditions();
						extraRecipe.setId(Integer.toString(rsExtra.getInt(1)));
						extraRecipe.setName(rsExtra.getString(2));
						extraVector.add(extraRecipe);
					}
					rec.setExtraVector(extraVector);
					psExtra.close();
					rsExtra.close();
				}
					
				
				if (midSouce>0)
				{
					Vector<RecipeAdditions> souceVector=new Vector<RecipeAdditions>();
					PreparedStatement psSouce = sc.getConnection().prepareStatement("SELECT R.Rid,R.Rname FROM  recipe_in_menu RM, recipe R, menu M WHERE M.mid=? AND M.mid=RM.mid AND RM.Rid=R.Rid; ");
					psSouce.setInt(1,midSouce);
					ResultSet rsSouce=psSouce.executeQuery();
					while(rsSouce.next())
					{
						RecipeAdditions souceRecipe=new RecipeAdditions();
						souceRecipe.setId(Integer.toString(rsSouce.getInt(1)));
						souceRecipe.setName(rsSouce.getString(2));
						souceVector.add(souceRecipe);
					}
					rec.setSouceVector(souceVector);
					psSouce.close();
					rsSouce.close();
				}
				
				//recipesOfBisnessMenu.add(rec);
				recipesOfSimpleMenu.add(rec);
				
			}
			
			menu.setRecipes(recipesOfSimpleMenu);
			menues.add(menu);
			rs4.close();
		}
		
		ps3.close();
		ps4.close();
		rs3.close();
		sc.getConnection().close();
		
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public void recieveRecipes()
	{
	
        // tut nado propisat sheiltu kotoraya vozvraschaet vse azmanot s opredelenogo stola -> tableNo 
		
		vecBon=new Vector<Bon>();
		
		SQLConnection sc=new SQLConnection();
		
		try {
		sc.getConnection().setAutoCommit(false);						        		
		PreparedStatement ps1=sc.getConnection().prepareStatement("SELECT OFT.comments,M.Mname,R.rname,RIM.price,OFT.mid,OFT.rid,ETT.eid,OFT.actionLevel,OFT.extraRid,OFT.extraName,OFT.souceRid,OFT.souceName,OFT.isFired,OFT.isSubmitted FROM employee_table_timeslot ETT,table_timeslot TT , order_from_a_table OFT, recipe R, Menu m, recipe_in_menu RIM WHERE TT.tid=? AND TT.status='opened' AND TT.orderId=OFT.orderId  AND OFT.rid=RIM.rid AND OFT.mid=RIM.mid AND RIM.rid=R.rid AND RIM.mid=M.mid AND M.mtype='simple' AND OFT.orderId=ETT.orderId ;");
		ps1.setString(1,tableNo);
		ResultSet rs1=ps1.executeQuery();
		while(rs1.next())
		{
			RecipeAdditions extra =new RecipeAdditions();
			RecipeAdditions souce=new RecipeAdditions();
			Menu menu=new Menu();
			Recipe recipe=new Recipe();
			Bon bon=new Bon();
			bon.setNew(false);
			bon.setComments(rs1.getString(1));
			bon.setMname(rs1.getString(2));
			menu.setName(rs1.getString(2));
			bon.setRname(rs1.getString(3));
			bon.setPrice(rs1.getString(4));
			//menu.setPrice(rs1.getString(4));
			bon.setMid(rs1.getString(5));
			menu.setId(rs1.getString(5));
			bon.setRid(rs1.getString(6));
			bon.setEid(rs1.getString(7));
			
			bon.setActionLevel(rs1.getString(8));
			extra.setId(Integer.toString(rs1.getInt(9)));
			extra.setName(rs1.getString(10));
			souce.setId(Integer.toString(rs1.getInt(11)));
			souce.setName(rs1.getString(12));
			bon.setIsFired(rs1.getInt(13));
			bon.setIsSubmitted(rs1.getInt(14));
			bon.setExtra(extra);
			bon.setSouce(souce);
			bon.setTid(tableNo);
			bon.setType("simple");
			menu.setType("simple");
			bon.setMenu(menu);
			vecBon.add(bon);
			
			
		}
		
		
		PreparedStatement ps2=sc.getConnection().prepareStatement("SELECT OFT.comments,M.Mname,R.rname,m.mprice,OFT.mid,OFT.rid,ETT.eid,OFT.actionLevel,OFT.extraRid,OFT.extraName,OFT.souceRid,OFT.souceName,OFT.isFired,OFT.isSubmitted FROM employee_table_timeslot ETT,table_timeslot TT , order_from_a_table OFT, recipe R, Menu m, recipe_in_menu RIM WHERE TT.tid=? AND TT.status='opened' AND TT.orderId=OFT.orderId  AND OFT.rid=RIM.rid AND OFT.mid=RIM.mid AND RIM.rid=R.rid AND RIM.mid=M.mid AND M.mtype='business' AND OFT.orderId=ETT.orderId ;");
		ps2.setString(1,tableNo);
		ResultSet rs2=ps2.executeQuery();
		while(rs2.next())
		{
			RecipeAdditions extra =new RecipeAdditions();
			RecipeAdditions souce=new RecipeAdditions();
			Menu menu=new Menu();
			Bon bon=new Bon();
			bon.setNew(false);
			bon.setComments(rs2.getString(1));
			bon.setMname(rs2.getString(2));
			menu.setName(rs2.getString(2));
			bon.setRname(rs2.getString(3));
			bon.setPrice(rs2.getString(4));
			menu.setPrice(rs2.getString(4));
			bon.setMid(rs2.getString(5));
			menu.setId(rs2.getString(5));
			bon.setRid(rs2.getString(6));
			bon.setEid(rs2.getString(7));
			
			bon.setActionLevel(rs2.getString(8));
			extra.setId(Integer.toString(rs2.getInt(9)));
			extra.setName(rs2.getString(10));
			souce.setId(Integer.toString(rs2.getInt(11)));
			souce.setName(rs2.getString(12));
			bon.setIsFired(rs2.getInt(13));
			bon.setIsSubmitted(rs2.getInt(14));
			bon.setExtra(extra);
			bon.setSouce(souce);
			bon.setTid(tableNo);
			bon.setType("business");
			menu.setType("business");
			bon.setMenu(menu);
			vecBon.add(bon);
		}
		
		
		PreparedStatement ps3=sc.getConnection().prepareStatement("SELECT TT.dateOpen,ETT.eid ,E.name,TT.timeOpen,TS.orderId,TT.no_of_persons,TT.no_of_children,TT.comments FROM  timeslot TS,table_timeslot TT, employee_table_timeslot ETT,Employee E WHERE TT.tid=? AND TT.status='opened' AND TT.tid=ETT.tid AND TT.dateOpen=ETT.dateOpen AND TT.timeOpen=ETT.timeOpen AND ETT.eid=E.eid AND TS.timeOpen=TT.timeOpen;");
		ps3.setString(1,tableNo);
		ResultSet rs3=ps3.executeQuery();
		if(rs3.next())
		{
			metadataOrder.setDate(rs3.getString(1));
			metadataOrder.setEid(rs3.getString(2));
			metadataOrder.setName(rs3.getString(3));
			metadataOrder.setTime(rs3.getString(4));
			metadataOrder.setOrderId(rs3.getString(5));
			metadataOrder.setNumOfPersons(rs3.getInt(6));
			metadataOrder.setNumOfChild(rs3.getInt(7));
			metadataOrder.setComments(rs3.getString(8));
			metadataOrder.setTid(tableNo);
			metadataOrder.setNew(false);
			
			
		}
		
		
		
		ps1.close();
		ps2.close();
		ps3.close();
		rs1.close();
		rs2.close();
		rs3.close();
		
		
		sc.getConnection().commit();
		 sc.getConnection().close();
		
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				sc.getConnection().rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}	
		
		
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	public Vector<Menu> getMenues() {
		return menues;
	}


	public void setMenues(Vector<Menu> menues) {
		this.menues = menues;
	}


	public Vector<Recipe> getRecipes() {
		return recipes;
	}


	public void setRecipes(Vector<Recipe> recipes) {
		this.recipes = recipes;
	}



	public Vector<Bon> getVecBon() {
		return vecBon;
	}



	public void setVecBon(Vector<Bon> vecBon) {
		this.vecBon = vecBon;
	}



	public MetaDataOrder getMetadataOrder() {
		return metadataOrder;
	}



	public void setOrder(MetaDataOrder order) {
		this.metadataOrder = order;
	}
	
	

}
